/** 
 * @Title: BrandMonthDao.java 
 * @Package com.zizo.autohome.sta.sales.brandmonth 
 * @Description: TODO
 * @author HeYan
 * @date 2016年10月27日 下午1:51:53 
 */
package com.zizo.autohome.sta.sales.brandmonth;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import com.zizo.util.ConnectionPool;

/**
 * @ClassName: BrandMonthDao
 * @Description: TODO
 * @author HeYan
 * @date 2016年10月27日 下午1:51:53
 * 
 */
public class BrandMonthDao {
	private static final Connection conn = ConnectionPool.getConnection("carvoice_biz", "localhost");

	public void createtable() {
		String sql1 = "create table biz_brsales_mn_temp like biz_brsales_mn";
		Statement st = null;
		try {
			st = conn.createStatement();
			st.executeUpdate(sql1);
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public void droptable() {
		String sql = "DROP TABLE IF EXISTS biz_brsales_mn;";
		String sql1 = "ALTER TABLE biz_brsales_mn_temp RENAME biz_brsales_mn;";
		Statement st = null;
		try {
			st = conn.createStatement();
			st.executeUpdate(sql);
			st.executeUpdate(sql1);
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public void getData() {
		String sql = "SELECT sales_cid,sales_cname,YEAR(sales_time),MONTH(sales_time),SUM(sales_cnt),sales_time FROM biz_sales GROUP BY sales_brname,YEAR(sales_time),MONTH(sales_time) ORDER BY sales_brdid,YEAR(sales_time),MONTH(sales_time)";
		Statement st = null;
		ResultSet rs = null;
		try {
			st = conn.createStatement();
			rs = st.executeQuery(sql);
			while (rs.next()) {
				int brdid = rs.getInt(1);
				String brname = rs.getString(2);
				int year = rs.getInt(3);
				int month = rs.getInt(4);
				int cnt = rs.getInt(5);
				String time = rs.getString(6);
				int temp = getRate(brdid, time);// 上一个月的销量
				float rate = 0;
				if (temp != 0) {
					rate = 100 * (cnt - temp) / (float) temp;
				}
				BrandMonth brand = new BrandMonth(brdid, brname, year, month, cnt, rate);
				if (rate == 0) {
					insertIs0(brand);
				} else {
					insertNot0(brand);
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public int getRate(int cid, String btime) {
		Statement st = null;
		ResultSet rs = null;
		String sql = "SELECT SUM(sales_cnt) FROM biz_sales WHERE sales_cid=" + cid + " AND sales_time=DATE_SUB('"
				+ btime + "', INTERVAL 1 month) GROUP BY sales_time;";
		int res = 0;
		try {
			st = conn.createStatement();
			rs = st.executeQuery(sql);
			if (rs.next()) {
				res = rs.getInt(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return res;
	}

	public void insertNot0(BrandMonth brand) {
		PreparedStatement ps = null;
		String sql = "INSERT INTO carvoice_biz.biz_brsales_month_temp(brdsales_brdid,brdsales_brdidname,brdsales_year,brdsales_month,brdsales_volume,brdsales_mrate) VALUES (?,?,?,?,?,ROUND(?,2));";
		try {
			ps = conn.prepareStatement(sql);
			ps.setInt(1, brand.getBrdid());
			ps.setString(2, brand.getBrname());
			ps.setInt(3, brand.getYear());
			ps.setInt(4, brand.getMonth());
			ps.setInt(5, brand.getVolume());
			ps.setFloat(6, brand.getMonthrate());
			ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public void insertIs0(BrandMonth brand) {
		PreparedStatement ps = null;
		String sql = "INSERT INTO carvoice_biz.biz_brsales_month_temp(brdsales_brdid,brdsales_brdidname,brdsales_year,brdsales_month,brdsales_volume) VALUES (?,?,?,?,?);";
		try {
			ps = conn.prepareStatement(sql);
			ps.setInt(1, brand.getBrdid());
			ps.setString(2, brand.getBrname());
			ps.setInt(3, brand.getYear());
			ps.setInt(4, brand.getMonth());
			ps.setInt(5, brand.getVolume());
			ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public static void main(String[] args) {
		BrandMonthDao dao = new BrandMonthDao();
		dao.createtable();
		dao.getData();
		dao.droptable();
	}
}
